USE perfume_db
GO

CREATE TABLE [role]
(
role_id INT IDENTITY(1,1) NOT NULL,
role_name NVARCHAR(255) NOT NULL,

CONSTRAINT [PK_role] PRIMARY KEY(role_id)
)

CREATE TABLE [user]
(
username NVARCHAR(255) NOT NULL,
password NVARCHAR(255) NOT NULL,
role_id INT NOT NULL,
name NVARCHAR(255),
phone_number NVARCHAR(255),
address NVARCHAR(255),

FOREIGN KEY (role_id) REFERENCES role(role_id),
CONSTRAINT [PK_user] PRIMARY KEY(username),
)

CREATE TABLE [export_check]
(
export_check_id INT IDENTITY(1,1) NOT NULL,
total FLOAT,
status NVARCHAR(255) NOT NULL,
username NVARCHAR(255) NOT NULL,
export_check_time DATETIME NOT NULL,

FOREIGN KEY (username) REFERENCES [user](username),
CONSTRAINT [PK_export_check] PRIMARY KEY(export_check_id)
)

CREATE TABLE [import_check]
(
import_check_id INT IDENTITY(1,1) NOT NULL,
total FLOAT,
import_check_time DATETIME NOT NULL,

CONSTRAINT [PK_import_check] PRIMARY KEY(import_check_id)
)

CREATE TABLE [category]
(
category_id INT IDENTITY(1,1) NOT NULL,
category_name NVARCHAR(255) NOT NULL,

CONSTRAINT [PK_category] PRIMARY KEY(category_id)
)

CREATE TABLE [product]
(
product_id INT IDENTITY(1,1) NOT NULL,
product_name NVARCHAR(255) NOT NULL,
detail_info TEXT,
image NVARCHAR(255),
price FLOAT NOT NULL,
inventory INT NOT NULL,
category_id INT NOT NULL,

FOREIGN KEY (category_id) REFERENCES category(category_id),
CONSTRAINT [PK_product] PRIMARY KEY(product_id)
)

CREATE TABLE [import_check_detail]
(
import_check_id INT NOT NULL,
product_id INT NOT NULL,
import_check_amount INT NOT NULL,

FOREIGN KEY (import_check_id) REFERENCES import_check(import_check_id),
FOREIGN KEY (product_id) REFERENCES product(product_id),
CONSTRAINT [PK_import_check_detail] PRIMARY KEY(import_check_id, product_id)
)

CREATE TABLE [export_check_detail]
(
export_check_id INT NOT NULL,
product_id INT NOT NULL,
export_check_amount INT NOT NULL,

FOREIGN KEY (export_check_id) REFERENCES export_check(export_check_id),
FOREIGN KEY (product_id) REFERENCES product(product_id),
CONSTRAINT [PK_export_check_detail] PRIMARY KEY(export_check_id, product_id)
)

CREATE TABLE [repo_detail]
(
history_id INT IDENTITY(1,1) NOT NULL,
check_id INT NOT NULL,
check_kind NVARCHAR(255) NOT NULL,
active_time Datetime NOT NULL,

CONSTRAINT [PK_repo_detail] PRIMARY KEY(history_id)
)


INSERT INTO [role](role_name) VALUES('Admin');
INSERT INTO [role](role_name) VALUES('User');

INSERT INTO [user](username, password, role_id) VALUES('admin', 'admin', 1);
INSERT INTO [user](username, password, role_id, name, phone_number, address) VALUES('user', 'user', 2, 'ban a', '012345', '123 hvt');